import pandas as pd
import psycopg2
from sqlalchemy import create_engine
import numpy as np


class BigData(object):
    def __init__(self):
        self.data = None

    #  从文本获取的内容存储到数据库中
    def run(self):
        df = pd.read_csv('D:\\app\\test\\20181127T1700-yd.csv')  #根据自己数据文件保存的路径填写(p.s.  python填写路径时，要么使用/，要么使用\\)
        engine = create_engine('postgresql://postgres:123456@localhost:5432/gis') #create_engine说明：dialect[+driver]://user:password@host/dbname[?key=value..]
        print(engine)
        data = pd.DataFrame(np.arange(15).reshape(3,5),index=['one','two','three'],columns=['a','b','c','d','e'])
        try:
            data.to_sql('tablename',engine,index=False,if_exists='append')
        except Exception as e:
            print(e)

    # 连接数据数据库，获取数据表内容打印
    def connectPG():
        conn = psycopg2.connect(database="gis", user="postgres", password="123456", host="127.0.0.1", port="5432")
        print("Opened database successfully")
        cur = conn.cursor()
        cur.execute("SELECT * FROM bld limit 10")
        rows = cur.fetchall()
        print(rows)
        for row in rows:
            print('name=' + str(row[0]) + ' address=' + str(row[1]) +
                  ' age=' + str(row[2]) + ' date=' + str(row[3]))

    # 查询数据库获取表内容，打印，返回DataFrame
    def sql2df(self):
        conn = psycopg2.connect(database="****", user="postgres", password="***", port="5432")
        with conn:
            cur = conn.cursor()
            cur.execute("select * from public.20160407_1")
            rows = cur.fetchall()
            t = pd.DataFrame(rows)
            print(t)
            return t


    # 将dataframe内容直接存储到数据库中
    def df2sql(self):
        df = pd.read_csv('D:\\app\\test\\20181127T1700-yd.csv')
        conn = psycopg2.connect(database="****", user="postgres", password="***", port="5432")
        df.to_sql('tableName', con=conn, flavor='pgsql')
